var express = require('express');
var router = express.Router();

const m_sql = require('../model/mysql.class.js');
const {
	Msg,
	Paths
} = require('../tools/message.js');

//轮播广告图路由
router.get('/banner', async function(req, res) {
	let sql = `select concat('${Paths}',coverimg) as coverimg from banner`;

	let [e, data] = await m_sql.exec(sql);

	if (e) {
		res.send(Msg(500, '轮播图查询失败'))
	} else {
		res.send(Msg(200, '轮播图查询成功', data))
	}
});

//所有一级商品路由
router.get('/catefirst', async (req, res) => {
	let sql = `select * from category_first`;

	let [e, data] = await m_sql.exec(sql);

	if (e) {
		res.send(Msg(500, '一级分类查询失败'))
	} else {

		await Promise.all(
			data.map(async (item) => {
				let sql =
					`SELECT
			second_name,
			second_id 
		FROM
			category_first AS f
			LEFT JOIN category_second AS s ON f.first_id = s.first_id 
		WHERE
			f.first_id = ${item.first_id}`;

				let [e, data] = await m_sql.exec(sql);
				item.category_second = data;


			})
		)

		await Promise.all(
			data.map(async (item) => {
				let sql =
					`SELECT
					thired_name,
					thired_id
				FROM
					category_first AS f
					LEFT JOIN category_second AS s ON f.first_id = s.first_id 
					LEFT JOIN category_thired AS t ON s.second_id=t.second_id
				WHERE
					f.first_id = ${item.first_id}`;
			
				let [e, data] = await m_sql.exec(sql);
				item.category_thired = data;


			})
		)

		res.send(Msg(200, '一级分类查询成功', data));
	}
});

//抢购活动
router.get('/panicbuying', async (req, res) => {
	let sql = 'select * from flash_sale';

	let [e, arr] = await m_sql.exec(sql);

	if (e) {
		res.send(Msg(500, '抢购活动查询失败'))
	} else {
		await Promise.all(
			arr.map(async (item) => {
				let sql =
					`SELECT
			f.goods_id,
			goods_name,
			concat('${Paths}',image_url)as image_url,
			goods_price 
		FROM
			flash_product AS f
			LEFT JOIN goods_list AS g ON f.goods_id = g.goods_id 
		WHERE
			flash_id = '${item.flash_id}'`;

				[e, data] = await m_sql.exec(sql);
				item.goods_list = data;

			})
		)
		res.send(Msg(200, '查询成功', arr))
	}
});

//首页排行榜：
router.get('/topbang', async (req, res) => {

	//随机查询4个三级类别：
	let sql = 'select thired_id,thired_name from category_thired  order by rand() limit 4';
	let [e, arr] = await m_sql.exec(sql);
	if (e) { //失败
		res.send(Msg(500, '排行榜查询失败'));
	} else {

		await Promise.all(
			arr.map(async (item) => {
				//根据每个三级类别id随机查询其下面的三个商品信息：
				sql =
					`select goods_id,goods_name,concat('${Paths}',image_url)as image_url,goods_price from goods_list where thired_id='${item.thired_id}'  order by rand() limit 3`;

				let [err, data] = await m_sql.exec(sql);
				item.goods_list = data;
			})
		);


		res.send(Msg(200, '排行榜查询成功', arr));
	}

});


//人气好货
router.get('/goodproduct', async (req, res) => {
	//查询评价最高的8个商品
	let sql =
		`select  g.goods_id,goods_name,image_url,goods_price from goods_eval as g
     left join goods_list as l on g.goods_id=l.goods_id
      group by g.goods_id  order by sum(eval_start) desc limit 8`;

	let [e, arr] = await m_sql.exec(sql);
	if (e) {
		res.send(Msg(500, '人气好货查询失败'));
	} else { //成功
		res.send(Msg(200, '人气好货查询成功', arr));
	}

});

module.exports = router;
